

function sqlQuery(data,eval){
	var dataAll=data;
	var ast=sqlParser.parse(eval).value;  
	var _this=this;  
	var getIsSum=function(){
		var list=ast.selectItems.value;
		for(var i=0;i<list.length;i++){
			if( list[i].type=='FunctionCall'&&(
				list[i].name=='count'||
				list[i].name=='sum'||
				list[i].name=='avg'||
				list[i].name=='min'||
				list[i].name=='max' 
			)){
				return true;
			}
		}
		return false;
	}
	var _getRow=function(data,items){
		var row={}; 
		 
		for(var i=0;i<items.length;i++){ 
			var isAll=items[i].type=='Identifier'&&items[i].value=='*';
			if(isAll){
				for(var name in data){
					row[name]=data[name];
				}
			}else{ 
				console.log(items[i]);
				row[items[i].type=='Identifier'?items[i].value:items[i].alias]=  
				( data  instanceof Array)?_getParam(data,items[i]):getParam(data,items[i]);
			}
			 
		}
		return row;
	} 
	var _getParam=function(row,param){
		if(param.type=='FunctionCall'){
			if(param.name=="max"){ 
				var value=getParam(row[0],param.params[0])-0;
				for(var i=1;i<row.length;i++){
					var v=getParam(row[i],param.params[0])-0;
					if(v>value){
						value=v;
					}
				} 
				return value;
			}else if(param.name=='min'){
				var value=getParam(row[0],param.params[0])-0;
				for(var i=1;i<row.length;i++){
					var v=getParam(row[i],param.params[0])-0;
					if(v<value){
						value=v;
					}
				} 
				return value;
			}else if(param.name=='avg') {
				var sum=0;
				for(var i=1;i<row.length;i++){
					var v=getParam(row[i],param.params[0])-0;
					sum+=v;
				} 
				return sum/row.length;
			}else if(param.name=='sum'){
				var sum=0;
				for(var i=0;i<row.length;i++){
					var v=getParam(row[i],param.params[0])-0;
					sum+=v;
				} 
				return sum;
			}else if(param.name=='count'){
				var count=0;
				for(var i=0;i<row.length;i++){
					if(param.params[0]=='*'){
						count++;
					}else{
						var value=getParam(row[i],param.params[0]); 
						if(value!=null){
							count++;
						}
					} 
				} 
				return count;
			}  else{
				return sqlQuery.prototype.fn[param.name].apply(row,_getParams(row,param.params)); 
			}
		}else if(param.type=='BitExpression'){ 
			var left=_getParam(row,param.left);
			var right=_getParam(row,param.right);
			if(param.operator=='+'){
				return left+right;
			}else if(param.operator=='-'){
				return left-right;
			}else if(param.operator=='*'){
				return left*right;
			}else if(param.operator=='/'){
				return left/right;
			}
		}else{
			return getParam(row[0],param);
		}
	}
	var _getParams=function(row,param){
		var data=[];
		for(var i=0;i<param.length;i++){
			data.push(_getParam(row,param[i]))
		}
		return data;
	}
	var getParam=function(row,param){
		if(param.type=='Number'){
			return param.value-0;
		}else if(param.type=='Identifier'){
			return row[param.value];
		}else if(param.type=='FunctionCall'){
			return evalFunction(row,param);
		}else if(param.type=='String'){
			return new Function('return '+param.value)();
		}else if(param.type=='CaseWhen'){
			var thenList=param.whenThenList.value; 
			for(var i=0;i<thenList.length;i++){
				var isFind=find(row,thenList[i].when); 
				if(isFind){
					return getParam(row,thenList[i].then);
				}
			}
			return getParam(row,param.else);
			
		}else if(param.type=='SimpleExprParentheses'){
			return find(row,param);
		}else if(param.type=='BitExpression'){
			var left=getParam(row,param.left);
			var right=getParam(row,param.right);
			if(param.operator=='+'){
				return left+right;
			}else if(param.operator=='-'){
				return left-right;
			}else if(param.operator=='*'){
				return left*right;
			}else if(param.operator=='/'){
				return left/right;
			}
		}
	}
	var getParams=function(row,param){
		var data=[];
		for(var i=0;i<param.length;i++){
			data.push(getParam(row,param[i]))
		}
		return data;
	}
	var evalFunction=function(row,$find){
		return sqlQuery.prototype.fn[$find.name].apply(row,getParams(row,$find.params));
	}
	var find=function(row,$find){ 
		if($find.type=="AndExpression"){
			return find(row,$find.left)&&
			find(row,$find.right)
		}else if($find.type=='FunctionCall'){
			return evalFunction(row,$find);
		}else if($find.type=='ExpressionList'){
			return find(row,$find.value[0]);
		}else if($find.type=='SimpleExprParentheses'){
			return find(row,$find.value);
		}else if($find.type=='ComparisonBooleanPrimary'){
			var left=getParam(row,$find.left);
			var right=getParam(row,$find.right);
			if($find.operator=='='){
				return left==right;
			}else if($find.operator=='>'){
				return left>right;
			}else if($find.operator=='>='){
				return left>=right;
			}else if($find.operator=='<'){
				return left<right;
			}else if($find.operator=='<='){
				return left<=right;
			}
		}else if($find.type=='OrExpression'){
			return find(row,$find.left)||
			find(row,$find.right)
		}else if($find.type=='IsNullBooleanPrimary'){
			var value=getParam(row,$find.value);
			if($find.hasNot=='not'){
				return value!=null;
			}
			return value==null; 
		}else if($find.type=='LikePredicate'){
			var left=getParam(row,$find.left)+"";
			var right=getParam(row,$find.right);
			var n=right.indexOf("%")==0;
			var e=right.lastIndexOf("%")==right.length-1;  
			if(n){
				right=right.substr(1,right.length);
			}
			if(e){
				right=right.substr(0,right.length-1);
			} 
			if(n&&e){
				return left.indexOf(right)!=-1;
			}else if(n){
				return left.lastIndexOf(right)+right.length==left.length; 
				 
			}else if(e){
				 return left.indexOf(right)==0; 
			}else{
				return left==right;
			} 
		}else if($find.type=='CaseWhen'){
			return getParam(row,$find);
		}
		else { 
			return false;
		}
	} 
	var getGroupKey=function(row,param){
		var data=[];
		for(var i=0;i<param.length;i++){ 
			data.push(getParam(row,param[i].value)); 
		}
		return data.join("");
	}
	function compare(name,sort, minor) {
		return function (o, p) {
			var a, b;
			var n1=1;
			var n2=-1;
			if(sort=="desc"){
				n1=-1;
				n2=1;
			}
			if (o && p && typeof o === 'object' && typeof p === 'object') {
				a = o[name];
				b = p[name];
				if (a === b) { 
					return typeof minor === 'function' ? minor(o, p) : 0;
				}
			  
				if(typeof(a)=="string"&&typeof(b)=="string"){
					return a.localeCompare(b,'zh-CN')>0?n1:n2;
				}
				else if (typeof a === typeof b) { 
					return a < b ? n2 : n1;
				}
				 
				return typeof a < typeof b ? n2 : n1;
			} else {
				thro("error");
			}
		}
	}
	var getSort=function(data){
		var sort=null;
		for(var i=data.length-1;i>=0;i--){
			sort=compare(data[i].value.value,data[i].sortOpt?data[i].sortOpt:"asc",sort)
		} 
		return sort;
	}
	var list=[];
	if(ast.where){
		for(var i=0;i<data.length;i++){ 
			if(find(data[i],ast.where,getParam)){
				list.push(data[i]);
			} 
		}
	}else{
		list=data;
	}
	var isGroup=false;
	if(ast.groupBy){ 
		isGroup=true;
		var map={};
		for(var i=0;i<list.length;i++){
			var key=getGroupKey(list[i],ast.groupBy.value);
			if(!map[key]){
				map[key]=[];
			}
			map[key].push(list[i]);
		}   
		list=[];
		for(var i in map){
			list.push(_getRow(map[i],ast.selectItems.value));
		} 
	} else{
		var findData=list;
		list=[]; 
		if(getIsSum()){
			list.push(_getRow(findData,ast.selectItems.value));
		}else{
			 
			for(var i=0;i<findData.length;i++){
				list.push(_getRow(findData[i],ast.selectItems.value));
			} 
		}
	 
	}
	if(ast.orderBy){
		var sort=getSort(ast.orderBy.value); 
		list.sort(sort);
	}  
	return list; 
	
}　
sqlQuery.prototype.fn={ 
	ifnull:function(){
		var value=arguments[0];
		var def=arguments[1];
		return value!=null?value:def;
	},
	concat:function(){
		var str="";
		for(var i=0;i<arguments.length;i++){
			str+=arguments[i];
		}
		return str;
	}
};　